package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.Pagination;
import cn.edu.sdjzu.xg.eagle.entity.Teacher;
import cn.edu.sdjzu.xg.eagle.exception.BysjException;
import cn.edu.sdjzu.xg.eagle.util.Condition;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDaoImpl implements TeacherDaoInterface {
    private static TeacherDaoInterface teacherDaoImpl = null;
    public TeacherDaoImpl() {
    }
    public static TeacherDaoInterface getInstance() {
        if (teacherDaoImpl == null) {
            teacherDaoImpl = new TeacherDaoImpl();
        }
        return teacherDaoImpl;
    }

    @Override
    public  boolean add(Connection connection, Teacher teacher) throws SQLException {
        String sql_insert = "INSERT INTO teacher (name,no)VALUES(?,?)";
        PreparedStatement pstmt_add = connection.prepareStatement(sql_insert,PreparedStatement.RETURN_GENERATED_KEYS);
        pstmt_add.setString(1,teacher.getName());
        pstmt_add.setString(2,teacher.getNo());
        int rowAffected = pstmt_add.executeUpdate();
        if(rowAffected>0){
            ResultSet generatedKeys = pstmt_add.getGeneratedKeys();
            if(generatedKeys.next()){
                teacher.setId(generatedKeys.getLong(1));
            }
        }
        return rowAffected>0;
    }
    @Override
    public Teacher find(Long id) throws SQLException {
        Teacher desiredTeacher = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "select * from teacher where id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()) {
            String name = resultSet_select_id.getString("name");
            String no = resultSet_select_id.getString("no");
            desiredTeacher = new Teacher(id, name, no);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredTeacher;
    }

    @Override
    public boolean delete(Connection connection, Long id) throws SQLException {
        String sql_delete = "DELETE FROM teacher WHERE id = ?";
        PreparedStatement pstmt_delete = connection.prepareStatement(sql_delete);
        pstmt_delete.setLong(1, id);
        int rowAffected = pstmt_delete.executeUpdate();
        return rowAffected > 0;
    }

    @Override
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList) throws SQLException, BysjException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"Teacher");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        System.out.println("Generated SQL: " + sql_select);
        ResultSet resultSet_select = pstmt_select.executeQuery();

        while(resultSet_select.next()){
            Long id = resultSet_select.getLong("id");
            String name = resultSet_select.getString("name");
            Long departmentId = resultSet_select.getLong("department_id");
            String no = resultSet_select.getString("no");
            //修改
            Department department = DepartmentDaoImpl.getInstance().find(departmentId);
            Teacher desiredTeacher = new Teacher(id,name,no,department);
            desiredTeachers.add(desiredTeacher);
        }
        System.out.println("Generated SQL: " + sql_select.toString());
        JdbcHelper.close(resultSet_select,pstmt_select);
        return desiredTeachers;
    }

    @Override
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher ");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            System.out.println("Generated Psgination SQL: " + sql_select.toString());
        }
        int totalNum = CommonDao.getInstance().count(connection, "Teacher", conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()){
            Long id = resultSet_select.getLong("id");
            String name = resultSet_select.getString("name");
            Long departmentId = resultSet_select.getLong("department_id");
            String no = resultSet_select.getString("no");
            //修改
            Department department = DepartmentDaoImpl.getInstance().find(departmentId);
            Teacher desiredTeacher = new Teacher(id,name,no,department);
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet_select,pstmt_select);
        return desiredTeachers;
    }
}


